package chess.database;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.eclipse.core.runtime.FileLocator;
import org.eclipse.core.runtime.Platform;
import org.osgi.framework.Bundle;

public class JavaDBConnection implements DBConnection {

	private static JavaDBConnection INSTANCE;

	public String driver = "org.apache.derby.jdbc.EmbeddedDriver";

	public String protocol = "jdbc:derby:";

	private Connection conn = null;

	private PreparedStatement psInsert = null;

	private String databasePath;

	public JavaDBConnection() {
		Bundle bundle = Platform.getBundle("Chess");
		try {
			databasePath = FileLocator.resolve(bundle.getEntry("/Databases/"))
					.getPath().trim();
		} catch (IOException e1) {
			e1.printStackTrace();
		}

		// connect();
	}

	public void connect() {
		loadDriver();

		String dbName = databasePath + "Derby";
		Properties props = new Properties();
		props.put("dovechess", "");
		try {
			conn = DriverManager.getConnection(protocol + dbName
					+ ";create=true", props);
			System.out.println("Connected to and created database " + dbName);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public int insertPlayer(String name, int rating) {
		int playerID = 0;
		try {
			PreparedStatement a = conn
					.prepareStatement("SELECT PLAYERID FROM DOVECHESS.PLAYERS WHERE NAME = ?");
			a.setString(1, name);

			ResultSet rs = a.executeQuery();

			if (rs.next()) {
				playerID = rs.getInt("PLAYERID");
				System.out.println("Existuje-ID:" + playerID);
			} else {

				Statement state = conn.createStatement();
				state.executeUpdate(
						"insert into DOVECHESS.PLAYERS (NAME,RATING) values ('"
								+ name + "'," + rating + ")",
						Statement.RETURN_GENERATED_KEYS);
				ResultSet keys = state.getGeneratedKeys();
				if (keys.next()) {
					playerID = keys.getInt(1);
				}
				System.out.println("NEexistuje-ID:" + playerID);
				System.out.println("Inserted player:" + name + " with rating "
						+ rating + " PLAYERID " + playerID);
			}
			a.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return playerID;
	}

	public int insertGame(int player1ID, int player2ID) {
		int gameID = 0;
		try {
			Statement state = conn.createStatement();
			state.executeUpdate(
					"insert into DOVECHESS.GAMES (PLAYER1ID,PLAYER2ID) values ("
							+ player1ID + "," + player2ID + ")",
					Statement.RETURN_GENERATED_KEYS);
			ResultSet keys = state.getGeneratedKeys();
			if (keys.next()) {
				gameID = keys.getInt(1);
			}
			System.out.println("Inserted game with players ID:" + player1ID
					+ " and " + player2ID + " with GAMEID " + gameID);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return gameID;
	}

	public void insertMove(String move, int gameDatabaseID) {
		try {
			Statement state = conn.createStatement();
			state
					.executeUpdate("insert into DOVECHESS.MOVES (move,gameID) values ('"
							+ move + "'," + gameDatabaseID + ")");
			System.out.println("MOVE INSERTED");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void setGameResult(int gameID, String result) {
		try {
			psInsert = conn
					.prepareStatement("update DOVECHESS.GAMES set result=? where gameid="
							+ gameID);
			psInsert.setString(1, result);
			psInsert.executeUpdate();
			System.out.println("Set result of game " + gameID + " to: "
					+ result);
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public static JavaDBConnection getInstance() {
		if (INSTANCE == null)
			INSTANCE = new JavaDBConnection();
		return INSTANCE;
	}

	/**
	 * Loads the appropriate JDBC driver for this environment/framework. For
	 * example, if we are in an embedded environment, we load Derby's embedded
	 * Driver, <code>org.apache.derby.jdbc.EmbeddedDriver</code>.
	 */
	private void loadDriver() {
		try {
			Class.forName(driver);
			System.out.println("Loaded the appropriate driver");
		} catch (ClassNotFoundException cnfe) {
			System.err.println("\nUnable to load the JDBC driver " + driver);
			System.err.println("Please check your CLASSPATH.");
			cnfe.printStackTrace(System.err);
		}
	}

}
